﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using WL.Model;

namespace WL.Dal
{
    class ProductDal
    {
        private static string tb = "wl_product";
        
        //public static void save(DataRow row)
        //{
        //    string sql = string .Format("insert into {0} values(NULL,'{1}',{2},{3},'{4}',{5},'')",
        //                                tb,row[0],row[1],row[2],row[3],row[4]);
        //    //System.Windows.Forms.MessageBox.Show(sql);
        //    SQLiteM.ExecuteInsert(CommandType.Text, sql);
        //}
        public static DataTable getAll() {
            return SQLiteM.GetDataSet(CommandType.Text, "select * from "+tb, null).Tables[0];
        }

        public static List<int> save(DataTable dt, string upfile, Table t)
        {
            List<int> indexs = new List<int>();//保存出错的列

            DbProviderFactory factory = SQLiteFactory.Instance;
            using (DbConnection conn = factory.CreateConnection())
            {
                //connection db
                conn.ConnectionString = "Data Source=test.db";
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();

                int j = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    DbCommand cmd = conn.CreateCommand();
                    string wenhao = "?"; //参数？个数
                    for (int count = 1; count <= t.fields.Count; count++)
                    {
                        wenhao += ",?";
                        cmd.Parameters.Add(cmd.CreateParameter());
                    }
                    cmd.Parameters.Add(cmd.CreateParameter());

                    cmd.CommandText = "insert into " + t.name + " values(" + wenhao + ")";
                    try
                    {
                        for(int index=0; index<=t.fields.Count;index++) {
                            cmd.Parameters[index].Value = row[index];
                        }
                        //此处bug，对于没有数据，为空的值，我们会报错，暂时不管，在这里加异常处理，添加不成功，记录下来
                        //cmd.Parameters[1].Value = Convert.ToInt32(row[1].ToString().Replace(",", ""));
                        //cmd.Parameters[2].Value = Convert.ToInt32(row[2].ToString().Replace(",", ""));
                        //cmd.Parameters[3].Value = row[3];
                        //cmd.Parameters[4].Value = Convert.ToDecimal(row[4].ToString().Replace(",", ""));
                        //cmd.Parameters[5].Value = upfile;
                        //cmd.Parameters[6].Value = Convert.ToInt32(row[5].ToString());

                        j++;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        indexs.Add(i);
                    }
                }
                trans.Commit();
            }

            return indexs;
        }

        public static List<int> save(DataTable dt, string upfile)
        {
            List<int> indexs = new List<int>();//保存出错的列

            DbProviderFactory factory = SQLiteFactory.Instance;
            using (DbConnection conn = factory.CreateConnection())
            {
                // 连接数据库  
                conn.ConnectionString = "Data Source=test.db";
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();

                int j = 0;
                //string ss = "";
                

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    DbCommand cmd = conn.CreateCommand();
                    for (int count = 1; count <= 7; count++)
                    {
                        cmd.Parameters.Add(cmd.CreateParameter());
                    }
                    

                    cmd.CommandText = string.Format("insert into {0} values(NULL,?,?,?,?,?,'',?,?)",
                                                    tb);
                    try
                    {
                        cmd.Parameters[0].Value = row[0];
                        //此处bug，对于没有数据，为空的值，我们会报错，暂时不管，在这里加异常处理，添加不成功，记录下来
                        cmd.Parameters[1].Value = Convert.ToInt32(row[1].ToString().Replace(",", ""));
                        cmd.Parameters[2].Value = Convert.ToInt32(row[2].ToString().Replace(",", ""));
                        cmd.Parameters[3].Value = row[3];
                        cmd.Parameters[4].Value = Convert.ToDecimal(row[4].ToString().Replace(",", ""));
                        cmd.Parameters[5].Value = upfile;
                        cmd.Parameters[6].Value = Convert.ToInt32(row[5].ToString());

                        j++;
                        cmd.ExecuteNonQuery();
                    } 
                    catch (Exception e)
                    {
                        indexs.Add(i);
                    }
                }

                trans.Commit();
                
                //catch (Exception e)
                //{
                //    System.Windows.Forms.MessageBox.Show(j+"");
                //    System.Windows.Forms.MessageBox.Show(ss);
                //    System.Windows.Forms.MessageBox.Show(e.Message);
                //    trans.Rollback();
                //}
 
            }
            return indexs;
                      
        }

          //internal static void FastInsertMany(DbConnection cnn)
          //  {
          //  using (DbTransaction dbTrans = cnn.BeginTransaction())
          //  {
          //  using (DbCommand cmd = cnn.CreateCommand())
          //  {
          //      try
          //     {
          //  cmd.CommandText = "INSERT INTO TestCase(MyValue) VALUES(?)";
          //  DbParameter Field1 = cmd.CreateParameter();
          //  cmd.Parameters.Add(Field1);
          //  for (int n = 0; n < 100000; n++)
          //  {
          //  Field1.Value = n + 100000;
          //  cmd.ExecuteNonQuery();
          //  }
          //  }
          //    dbTrans.Commit();
          //     }
          //     catch
          //     {
          //       dbTrans.RollBack();
          //     }
          //  }
          //  }
        internal static int update(Product p)
        {
           // string sql = "update "+tb+" set no=?,freeStock=?,packQty=?,manufacturer=?,price=?,remark=? where id="+p.id;
            string sql = string.Format( "update " + tb + " set no='{0}',freeStock={1},packQty={2},manufacturer='{3}',price={4},remark='{5}',upfile='{6}',line={7} where id=" + p.id,
                                        p.no, p.freeStock, p.packQty, p.manufacturer,p.price,p.remark, p.upfile,p.line );

            return SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
            //return false;
        } 

        /// <summary>
        ///根据条件获取数据
        /// </summary>
        /// <param name="key">关键字（供应商、型号）</param>
        /// <param name="upfile"></param>
        /// <returns></returns>
        internal static DataTable getDatas(string key, string upfile)
        {
            string where = " where 1=1";
            if (key != null)
            {
                where += " and (no like '%" + key + "%' or manufacturer like '%"+key+"%')";
            }
            if (upfile != null)
            {
                where += " and upfile='"+upfile+"'";
            }

            string sql = "select * from " + tb + where;
            return SQLiteM.GetDataSet(CommandType.Text, sql, null).Tables[0];
        }


        /// <summary>
        /// 根据id删除
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        internal static int delete(int iid)
        {
            string sql = "delete from " + tb + " where id="+iid;// like '%" + no + "%'";

            return SQLiteM.ExecuteNonQuery(CommandType.Text,sql);
        }
        /// <summary>
        /// 获取数据的文件名列表
        /// </summary>
        /// <returns></returns>
        internal static string[] getFileNames()
        {
            string sql = "select distinct upfile from wl_product";
            DataTable ds = SQLiteM.GetDataSet(CommandType.Text, sql).Tables[0];

            List<string> l = new List<string>();

            foreach (DataRow row in ds.Rows) {
                l.Add(row[0].ToString());
            }
            return l.ToArray();
        }

        /// <summary>
        /// 根据文件名删除数据
        /// </summary>
        /// <param name="upfile"></param>
        internal static void deleteByUpfile(string upfile)
        {
            string sql = "delete from " + tb + " where upfile=?";// like '%" + no + "%'";
            SQLiteParameter[] param= SQLiteM.GetParams(new string[] { "upfile" }, new object[] {upfile});

            SQLiteM.ExecuteNonQuery(CommandType.Text, sql, param);
        }
    }
}
